const { getDbPool } = require('../infra/dbClient');
const db = getDbPool();
const { getLogger } = require('../logger');
const logger = getLogger('models.departmentModel');
const  CodeService  = require('../services/codeService');

class DepartmentModel {
    // 获取所有部门
    static async getAllDepartments(page = 1, limit = 10, filters = {}) {
        const offset = (page - 1) * limit;
        let whereClause = 'WHERE d.is_delete = 0';
        let params = [];

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND d.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND d.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.type !== undefined) {
            whereClause += ' AND d.type = ?';
            params.push(filters.type);
        }
        if (filters.parent_id !== undefined) {
            whereClause += ' AND d.parent_id = ?';
            params.push(filters.parent_id);
        }

        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.description,
                d.status, 
                d.is_protected,
                d.created_at, 
                d.updated_at,
                (SELECT name FROM user WHERE id = d.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = d.update_by AND is_delete = 0) as update_by_name,
                d.manager_id,
                d.phone,
                d.email,
                d.address,
                d.short_name,
                d.responsibility,
                u.name as manager_name,
                p.name as parent_name,
                p.code as parent_code
            FROM department d
            LEFT JOIN department p ON d.parent_id = p.id AND p.is_delete = 0
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            ${whereClause}
            ORDER BY d.parent_id ASC, d.id ASC
            LIMIT ${db.escape(limit)} OFFSET ${db.escape(offset)}
        `;

        try {
            const [rows] = await db.execute(sql, params);
            return rows;
        } catch (error) {
            logger.error('获取所有部门失败', { error });
            throw error;
        }
    }

    // 获取部门总数
    static async getDepartmentCount(filters = {}) {
        let whereClause = 'WHERE d.is_delete = 0';
        let params = [];

        // 构建过滤条件
        if (filters.name) {
            whereClause += ' AND d.name LIKE ?';
            params.push(`%${filters.name}%`);
        }
        if (filters.code) {
            whereClause += ' AND d.code LIKE ?';
            params.push(`%${filters.code}%`);
        }
        if (filters.type !== undefined) {
            whereClause += ' AND d.type = ?';
            params.push(filters.type);
        }
        if (filters.parent_id !== undefined) {
            whereClause += ' AND d.parent_id = ?';
            params.push(filters.parent_id);
        }

        const sql = `SELECT COUNT(*) as total FROM department d ${whereClause}`;

        try {
            const [rows] = await db.execute(sql, params);
            return rows[0].total;
        } catch (error) {
            logger.error('获取部门总数失败', { error });
            throw error;
        }
    }

    // 根据ID获取部门
    static async findById(id) {
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                (SELECT name FROM user WHERE id = d.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = d.update_by AND is_delete = 0) as update_by_name,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                d.short_name,
                d.responsibility,
                d.short_name,
                d.responsibility,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.id = ? AND d.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据ID获取部门失败', { error });
            throw error;
        }
    }

    // 根据编码获取部门
    static async findByCode(code) {
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                (SELECT name FROM user WHERE id = d.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = d.update_by AND is_delete = 0) as update_by_name,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                d.short_name,
                d.responsibility,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.code = ? AND d.is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [code]);
            return rows[0] || null;
        } catch (error) {
            logger.error('根据编码获取部门失败', { error });
            throw error;
        }
    }

    // 根据父级ID获取子部门
    static async findByParentId(parentId) {
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                (SELECT name FROM user WHERE id = d.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = d.update_by AND is_delete = 0) as update_by_name,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                d.short_name,
                d.responsibility,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.parent_id = ? AND d.is_delete = 0
            ORDER BY d.id ASC
        `;

        try {
            const [rows] = await db.execute(sql, [parentId]);
            return rows;
        } catch (error) {
            logger.error('根据父级ID获取子部门失败', { error });
            throw error;
        }
    }

    // 创建部门
    static async create(departmentData, currentUserId = null) {
        let { name, type, code, parent_id ,manager_id,phone,email,address,description, short_name, responsibility, create_by = currentUserId } = departmentData;
        // 如果没有提供code，则自动生成部门编码（使用generateUniqueCode确保编码唯一）
        if (!code) {
            try {
                // 使用CodeService生成唯一编码
                code = await CodeService.generateUniqueCode('DEPARTMENT_CODE');
                logger.info('自动生成部门编码', { code, departmentName: name });
            } catch (error) {
                logger.error('生成部门编码失败', { error, departmentName: name });
                throw new Error('生成部门编码失败：' + error.message);
            }
        } else {
            // 如果提供了code，则检查编码是否已存在
            const codeExists = await CodeService.checkCodeExists('DEPARTMENT_CODE', code);
            if (codeExists) {
                logger.error('部门编码已存在', { code, departmentName: name });
                throw new Error(`部门编码 ${code} 已存在，请更换其他编码`);
            }
        }
        
        // 计算path
        let path = '';
        if (parent_id) {
            const parentDept = await this.findById(parent_id);
            if (parentDept) {
                path = parentDept.path ? `${parentDept.path},${parent_id}` : parent_id.toString();
            }
        }

        const sql = `
            INSERT INTO department (name, type, code, parent_id, path, manager_id, phone, email, address, description, short_name, responsibility, create_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;

        try {
            // 确保所有参数都不会是undefined，将undefined转换为null
            // 特别注意：type字段不能为空，默认为0(组织)
            const safeParams = [
                name || null,
                type !== undefined && type !== null ? type : 0,
                code || null,
                parent_id || null,
                path || null,
                manager_id || null,
                phone || null,
                email || null,
                address || null,
                description || null,
                short_name || '',
                responsibility || '',
                create_by || null
            ];
            const [result] = await db.execute(sql, safeParams);
            return result.insertId;
        } catch (error) {
            logger.error('创建部门失败', { error });
            throw error;
        }
    }

    // 更新部门
    static async update(id, updateData, currentUserId = null) {
        const { name, type, code, parent_id ,manager_id,phone,email,address,description, short_name, responsibility, update_by = currentUserId } = updateData;
        // 如果更新了parent_id，需要重新计算path
        let path = '';
        if (parent_id !== undefined) {
            if (parent_id) {
                const parentDept = await this.findById(parent_id);
                if (parentDept) {
                    path = parentDept.path ? `${parentDept.path},${parent_id}` : parent_id.toString();
                }
            } else {
                path = '';
            }
        }

        // 处理undefined参数，转换为null
        const safeName = name !== undefined ? name : null;
        const safeType = type !== undefined ? type : null;
        const safeCode = code !== undefined ? code : null;
        const safeParentId = parent_id !== undefined ? parent_id : null;
        const safeManagerId = manager_id !== undefined ? manager_id : null;
        const safePhone = phone !== undefined ? phone : null;
        const safeEmail = email !== undefined ? email : null;
        const safeAddress = address !== undefined ? address : null;
        const safeRemark = description !== undefined ? description : null;
        const safeShortName = short_name !== undefined ? short_name : null;
        const safeResponsibility = responsibility !== undefined ? responsibility : null;

        const sql = `
            UPDATE department 
            SET name = ?, type = ?, code = ?, parent_id = ?, path = ?, manager_id = ?, phone = ?, email = ?, address = ?, description = ?, short_name = ?, responsibility = ?, update_by = ?, updated_at = NOW()
            WHERE id = ? AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [safeName, safeType, safeCode, safeParentId, path, safeManagerId, safePhone, safeEmail, safeAddress, safeRemark, safeShortName, safeResponsibility, update_by || null, id]);
            
            // 如果更新了parent_id，需要更新所有子部门的path
            if (parent_id !== undefined) {
                await this.updateChildrenPath(id);
            }
            
            return result.affectedRows;
        } catch (error) {
            logger.error('更新部门失败', { error });
            throw error;
        }
    }

    // 更新子部门的path
    static async updateChildrenPath(parentId) {
        const sql = `
            UPDATE department 
            SET path = CONCAT(?, ',', ?)
            WHERE path LIKE CONCAT('%', ?, '%') AND id != ?
        `;

        try {
            const parentDept = await this.findById(parentId);
            if (parentDept) {
                const newPath = parentDept.path ? `${parentDept.path},${parentId}` : parentId.toString();
                await db.execute(sql, [newPath, parentId, parentId, parentId]);
            }
        } catch (error) {
            logger.error('更新子部门path失败', { error });
            throw error;
        }
    }

    // 删除部门（软删除）
    static async deleteById(id, currentUserId = null) {
        const sql = `
            UPDATE department 
            SET is_delete = 1, update_by = ?, updated_at = NOW()
            WHERE id = ? AND is_delete = 0 
        `;

        try {
            const [result] = await db.execute(sql, [currentUserId || null, id]);
            return result.affectedRows;
        } catch (error) {
            logger.error('删除部门失败', { error });
            throw error;
        }
    }

    // 获取部门树结构
    static async getDepartmentTree() {
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                (SELECT name FROM user WHERE id = d.create_by AND is_delete = 0) as create_by_name,
                (SELECT name FROM user WHERE id = d.update_by AND is_delete = 0) as update_by_name,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                d.short_name,
                d.responsibility,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.is_delete = 0
            ORDER BY d.path ASC
        `;
        
        try {
            const [departments] = await db.execute(sql);
            return this.buildTree(departments);
        } catch (error) {
            logger.error('获取部门树结构失败', { error });
            throw error;
        }
    }

    // 构建树结构
    static buildTree(departments, parentId = null) {
        const tree = [];
        
        for (const dept of departments) {
            if (dept.parent_id === parentId) {
                const children = this.buildTree(departments, dept.id);
                if (children.length > 0) {
                    dept.children = children;
                }
                tree.push(dept);
            }
        }
        
        return tree;
    }

    // 获取部门的所有子部门（包括子子部门）
    static async getAllChildren(id) {
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.path LIKE CONCAT('%', ?, '%') AND d.id != ? AND d.is_delete = 0
            ORDER BY d.path ASC
        `;

        try {
            const [rows] = await db.execute(sql, [id, id]);
            return rows;
        } catch (error) {
            logger.error('获取所有子部门失败', { error });
            throw error;
        }
    }

    // 获取部门的所有父部门
    static async getAllParents(id) {
        const dept = await this.findById(id);
        if (!dept || !dept.path) {
            return [];
        }

        const parentIds = dept.path.split(',').map(id => parseInt(id.trim()));
        const sql = `
            SELECT 
                d.id, 
                d.name, 
                d.type, 
                d.code, 
                d.parent_id, 
                d.path,
                d.created_at, 
                d.updated_at,
                d.manager_id,
                d.phone,
                d.status, 
                d.is_protected,
                d.email,
                d.address,
                u.name as manager_name
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            WHERE d.id IN (${parentIds.map(() => '?').join(',')}) AND d.is_delete = 0
            ORDER BY FIELD(d.id, ${parentIds.map(() => '?').join(',')})
        `;

        try {
            const [rows] = await db.execute(sql, [...parentIds, ...parentIds]);
            return rows;
        } catch (error) {
            logger.error('获取所有父部门失败', { error });
            throw error;
        }
    }

    // 检查部门编码是否存在（兼容旧代码，内部调用CodeService）
    static async isCodeExists(code, excludeId = null) {
        try {
            // 如果提供了excludeId，我们需要特殊处理，先使用CodeService检查
            let exists = await CodeService.checkCodeExists('DEPARTMENT_CODE', code);
            
            // 如果CodeService发现存在，但提供了excludeId，我们需要进一步检查是否是要排除的记录
            if (exists && excludeId) {
                const sql = 'SELECT id FROM department WHERE code = ? AND id = ? AND is_delete = 0';
                const [rows] = await db.execute(sql, [code, excludeId]);
                // 如果找到的是要排除的记录，则返回false
                if (rows.length > 0) {
                    return false;
                }
            }
            
            return exists;
        } catch (error) {
            logger.error('检查部门编码是否存在失败', { error, code, excludeId });
            throw error;
        }
    }

    // 检查部门是否可以删除
    static async isDeletable(id) {
        const sql = `
            SELECT 
                COUNT(child.id) as children_count,
                COUNT(u.id) as users_count
            FROM department d
            LEFT JOIN department child ON d.id = child.parent_id AND child.is_delete = 0
            LEFT JOIN user u ON d.id = u.department_id AND u.is_delete = 0
            WHERE d.id = ? AND d.is_delete = 0
            GROUP BY d.id, d.is_protected
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            if (rows.length === 0) return false;
            
            const dept = rows[0];
            return dept.children_count === 0 && dept.users_count === 0;

            if (rows.length === 0) return false;
            
            const user = rows[0];
            return user.is_protected === 0; // 只有非保护用户才能删除
        } catch (error) {
            logger.error('检查部门是否可删除失败', { error });
            throw error;
        }
    }

    // 检查部门是否绑定有用户
    // 检查是否有用户绑定了该部门
    static async isBindUser(departmentId) {
        const sql = 'SELECT COUNT(*) AS count FROM user WHERE department_id = ?';
        try {
            const [result] = await db.execute(sql, [departmentId]);
            return result[0].count > 0;
        } catch (error) {
            logger.error('检查用户绑定状态失败', { error });
            throw error;
        }
    }

    // 获取部门用户数量
    static async getUserCount(id) {
        const sql = `
            SELECT COUNT(*) as count 
            FROM user 
            WHERE department_id = ? AND is_delete = 0
        `;

        try {
            const [rows] = await db.execute(sql, [id]);
            return rows[0].count;
        } catch (error) {
            logger.error('获取部门用户数量失败', { error });
            throw error;
        }
    }

    // 批量更新部门用户
    static async updateUsersDepartment(userIds, departmentId) {
        const sql = `
            UPDATE user 
            SET department_id = ?, updated_at = NOW()
            WHERE id IN (${userIds.map(() => '?').join(',')}) AND is_delete = 0
        `;

        try {
            const [result] = await db.execute(sql, [departmentId, ...userIds]);
            return result.affectedRows;
        } catch (error) {
            logger.error('批量更新部门用户失败', { error });
            throw error;
        }
    }

    // 获取部门统计信息
    static async getDepartmentStats() {
        const sql = `
            SELECT 
                d.id,
                d.name,
                d.type,
                d.code,
                d.parent_id,
                d.manager_id,
                d.phone,
                d.email,
                d.address,
                u.name as manager_name,
                COUNT(u2.id) as user_count,
                COUNT(child.id) as children_count
            FROM department d
            LEFT JOIN user u ON d.manager_id = u.id AND u.is_delete = 0
            LEFT JOIN user u2 ON d.id = u2.department_id AND u2.is_delete = 0
            LEFT JOIN department child ON d.id = child.parent_id AND child.is_delete = 0
            WHERE d.is_delete = 0
            GROUP BY d.id, d.name, d.type, d.code, d.parent_id, d.manager_id, d.phone, d.email, d.address, u.name
            ORDER BY d.parent_id ASC, d.id ASC
        `;

        try {
            const [rows] = await db.execute(sql);
            return rows;
        } catch (error) {
            logger.error('获取部门统计信息失败', { error });
            throw error;
        }
    }

    // 获取可选的部门负责人列表
    static async getManagerList(departmentId = null) {
        let sql = `
            SELECT 
                u.id, 
                u.username, 
                u.name, 
                u.mobile, 
                u.detail_address,
                u.phone,
                u.email,
                u.address,
                d.name as department_name,
                u.department_id
            FROM user u
            LEFT JOIN department d ON u.department_id = d.id AND d.is_delete = 0
            WHERE u.is_delete = 0 AND u.status = 0 AND u.is_protected = 0
        `;
        const params = [];

        // 如果提供了部门ID，则添加过滤条件
        if (departmentId !== null) {
            sql += ' AND u.department_id = ?';
            params.push(departmentId);
        }

        sql += ' ORDER BY u.name ASC';

        try {
            const [rows] = await db.execute(sql, params);
            // 确保返回的数据格式正确
            return rows.map(row => ({
                ...row,
                // 确保department_id类型正确
                department_id: row.department_id ? Number(row.department_id) : null
            }));
        } catch (error) {
            logger.error('获取部门负责人列表失败', { error, departmentId });
            throw error;
        }
    }

}

module.exports = DepartmentModel;
